package com.plasticcode.performance;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import com.plasticcode.log.domain.RunType;

public class JDBCTestCase {
    private static Connection sConnection;
    private static IdAllocator sIdAllocator;
    private static PreparedStatement sInsertRunnerStatement;
    private static PreparedStatement sUpdateRunnerStatement;
    private static PreparedStatement sQueryRunnerStatement;
    private static PreparedStatement sInsertRunStatement;
    private static PreparedStatement sUpdateRunStatement;
    private static PreparedStatement sQueryRunStatement;
    
    @BeforeClass
    public static void setUpDBResources() throws Exception {
        try {
            sIdAllocator = new IdAllocator(getConnection());        
            
            sConnection = getConnection();
    
            sInsertRunnerStatement = sConnection.prepareStatement("insert into RUNNER values (?, ?, ?, ?, ?, ?, ?, ?, ?)");
            sUpdateRunnerStatement = sConnection.prepareStatement("update RUNNER set NAME = ?, AGE = ?, HOUSE_NAME = ?, HOUSE_NUMBER = ?, STREET = ?, TOWN = ?, CITY = ?, POSTCODE = ? where ID = ?");
            sQueryRunnerStatement = sConnection.prepareStatement("select ID, NAME, AGE, HOUSE_NAME, HOUSE_NUMBER, STREET, TOWN, CITY, POSTCODE from RUNNER where ID = ?");
            
            sInsertRunStatement = sConnection.prepareStatement("insert into RUN values (?, ?, ?, ?, ?, ?)");
            sUpdateRunStatement = sConnection.prepareStatement("update RUN set DESCRIPTION = ?, DISTANCE = ?, TYPE = ?, HEART_RATE = ? where ID = ?");        
            sQueryRunStatement = sConnection.prepareStatement("select ID, DESCRIPTION, DISTANCE, TYPE, HEART_RATE from RUN where RUNNER_ID = ?");
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    @AfterClass
    public static void tearDownDBResources() throws SQLException {
        sConnection.close();
    }
    
    @Test
    public void testInsert() throws Exception {
        PerformanceController controller = new PerformanceController(
                "JDBC insert performance", new MeasuredOperation() {
            public void setup(int children) {}

            public void run(int children, int samples) throws Exception {
                insert(children, samples);                
            }
        });
        controller.measure();
    }

    private long insert(int numOfChildren, int samples) throws SQLException {
        long runnerId = 0;
        for (int i = 0; i < samples; i++) {
            runnerId = sIdAllocator.allocateId();
            sInsertRunnerStatement.setLong(1, runnerId);
            sInsertRunnerStatement.setString(2, "Keith Bradford");
            sInsertRunnerStatement.setInt(3, 26);
            sInsertRunnerStatement.setNull(4, Types.VARCHAR);
            sInsertRunnerStatement.setInt(5, 99);
            sInsertRunnerStatement.setString(6, "Long Street");
            sInsertRunnerStatement.setString(7, "Nowhere");
            sInsertRunnerStatement.setString(8, "London");
            sInsertRunnerStatement.setString(9, "EC2 XXX");
            sInsertRunnerStatement.executeUpdate();
            
            for (int j = 0; j < numOfChildren; j++) {
                sInsertRunStatement.setLong(1, sIdAllocator.allocateId());
                sInsertRunStatement.setString(2, "Long run");
                sInsertRunStatement.setDouble(3, 10.0);
                sInsertRunStatement.setInt(4, RunType.STEADY.ordinal());
                sInsertRunStatement.setInt(5, 140);
                sInsertRunStatement.setLong(6, runnerId);
                sInsertRunStatement.executeUpdate();
            }
            
            sConnection.commit();
        }
        return runnerId;
    }
    
    
    @Test
    public void testUpdate() throws Exception {
        PerformanceController controller = new PerformanceController(
                "JDBC update performance", new MeasuredOperation() {
            List<Long> runIds;
            long runnerId;
            
            public void setup(int children) throws Exception {
                runnerId = insert(children, 1);
                runIds = getRunIds(runnerId);
            }

            public void run(int children, int samples) throws Exception {
                update(runnerId, runIds, samples);            
            }
        });
        controller.measure();
    }    
    
    private void update(long runnerId, List<Long> runIds, int samples) throws SQLException {
        for (int i = 0; i < samples; i++) {
            sUpdateRunnerStatement.setString(1, "Amended" + i);
            sUpdateRunnerStatement.setInt(2, i);
            sUpdateRunnerStatement.setNull(3, Types.VARCHAR);
            sUpdateRunnerStatement.setInt(4, i);
            sUpdateRunnerStatement.setString(5, "Amended" + i);
            sUpdateRunnerStatement.setString(6, "Amended" + i);
            sUpdateRunnerStatement.setString(7, "Amended" + i);
            sUpdateRunnerStatement.setString(8, "CHG " + i);
            
            sUpdateRunnerStatement.setLong(9, runnerId); // Key
            sUpdateRunnerStatement.executeUpdate();
            
            for (long runId : runIds) {
                sUpdateRunStatement.setString(1, "Amended" + i);
                sUpdateRunStatement.setDouble(2, 100.0 + i);
                sUpdateRunStatement.setInt(3, i % RunType.values().length);
                sUpdateRunStatement.setInt(4, i);
                sUpdateRunStatement.setLong(5, runId); // Key
                sUpdateRunStatement.executeUpdate();
            }
            
            sConnection.commit();
        }
    }
    
    @Test
    public void testQuery() throws Exception {
        PerformanceController controller = new PerformanceController(
                "JDBC query performance", new MeasuredOperation() {
            long runnerId;
            
            public void setup(int children) throws Exception {
                runnerId = insert(children, 1);
            }

            public void run(int children, int samples) throws Exception {
                query(runnerId, samples);        
            }
        });
        controller.measure();
    }

    private void query(long runnerId, int samples) throws SQLException {
        for (int i = 0; i < samples; i++) {
            sQueryRunnerStatement.setLong(1, runnerId);
            ResultSet resultSet = sQueryRunnerStatement.executeQuery();  
            while (resultSet.next()) {
                resultSet.getLong(1);
                resultSet.getString(2);
                resultSet.getInt(3);
                resultSet.getString(4);
                resultSet.getInt(5);
                resultSet.getString(6);
                resultSet.getString(7);
                resultSet.getString(8);
                resultSet.getString(9);
            }
            resultSet.close();
            
            getRunIds(runnerId);
        }
    }
    
    private List<Long> getRunIds(long runnerId) throws SQLException {
        List<Long> runIds = new ArrayList<Long>();
        
        sQueryRunStatement.setLong(1, runnerId);
        ResultSet resultSet = sQueryRunStatement.executeQuery();  
        while (resultSet.next()) {
            runIds.add(resultSet.getLong(1));
            resultSet.getString(2);
            resultSet.getInt(3);
            resultSet.getInt(4);
            resultSet.getInt(5);
        }
        resultSet.close();
        return runIds;
    }
    
    private static Connection getConnection() throws Exception {
        Class.forName("org.apache.derby.jdbc.ClientDriver");
        Connection connection = DriverManager.getConnection("jdbc:derby://localhost/runningdb", "test", "test");
        connection.setAutoCommit(false);
        return connection;
    }
    
    private static class IdAllocator {
        private Connection connection;    
        private PreparedStatement queryStatement; 
        private PreparedStatement updateStatement;
        
        public IdAllocator(Connection connection) throws SQLException {
            this.connection = connection;
            
            queryStatement = connection.prepareStatement("select NEXT_HI from HIBERNATE_UNIQUE_KEY");
            updateStatement = connection.prepareStatement("update HIBERNATE_UNIQUE_KEY set NEXT_HI = ?");
        }
        
        public long allocateId() throws SQLException {
            ResultSet query = queryStatement.executeQuery();
            query.next();
            long nextId = query.getLong(1);
            
            updateStatement.setLong(1, nextId + 1);
            updateStatement.executeUpdate();
            connection.commit();
            
            return nextId;
        }
    }
}
